package cn.hairui.blog.dao;

import cn.hairui.blog.entity.Artical;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author laoganbu
 * @since 2020-04-06
 */
@Mapper
public interface ArticalMapper extends BaseMapper<Artical> {
    @Select("select * from artical order by #{key} desc limit 0,#{count}")
    List<Artical> listTop(int count, String key);

    @Select("select count(1) from artical")
    int count();

    @Select("SELECT\n" +
            "  t1.id,\n" +
            "  t1.title,\n" +
            "  t1.summary,\n" +
            "  t1.create_id,\n" +
            "  t1.create_date,\n" +
            "  t1.view_times,\n" +
            "  t1.like_times,\n" +
            "  t1.type_id,t1.index_picture,\n" +
            "  t2.nickname AS create_name,\n" +
            "  t2.prophoto AS prophoto,\n" +
            "  t3.name AS type_name\n" +
            "FROM artical t1\n" +
            "  LEFT JOIN user t2\n" +
            "    ON t1.create_id = t2.ACCOUNT\n" +
            "  LEFT JOIN TYPE t3\n" +
            "    ON t1.type_id = t3.id\n" +
            "WHERE t1.show_flag = #{showFlag} order by t1.id desc")
    List<Map> selectByShowFlag( String showFlag);

    @Select("SELECT\n" +
            "    id,\n" +
            "    title,\n" +
            "    (\n" +
            "        SELECT\n" +
            "            user.nickname\n" +
            "        FROM\n" +
            "            USER\n" +
            "        WHERE\n" +
            "            artical.create_id=user.account) AS createname,\n" +
            "    (\n" +
            "        SELECT\n" +
            "            name\n" +
            "        FROM\n" +
            "            type\n" +
            "        WHERE\n" +
            "            artical.type_id=type.id) AS type,\n" +
            "    CASE\n" +
            "        WHEN show_flag='Y'\n" +
            "        THEN 'checked'\n" +
            "        ELSE ''\n" +
            "    END         AS showflag,\n" +
            "    create_time AS createtime\n" +
            "FROM\n" +
            "    artical\n" +
            "WHERE\n" +
            "    create_id=#{account} order by artical.id desc")
    List<Map> selectByAccount(String account);

    @Select("SELECT\n" +
            "  t1.id,\n" +
            "  t1.title,\n" +
            "  t1.summary,\n" +
            "  t1.create_id,\n" +
            "  t1.create_date,\n" +
            "  t1.view_times,\n" +
            "  t1.like_times,\n" +
            "  t1.type_id,t1.index_picture,\n" +
            "  t2.nickname AS create_name,\n" +
            "  t2.prophoto AS prophoto,\n" +
            "  t3.name AS type_name\n" +
            "FROM artical t1\n" +
            "  LEFT JOIN user t2\n" +
            "    ON t1.create_id = t2.ACCOUNT\n" +
            "  LEFT JOIN TYPE t3\n" +
            "    ON t1.type_id = t3.id\n" +
            "WHERE t1.type_id=${typeId}\n" +
            "    AND t1.show_flag = #{showFlag} order by t1.id desc")
    List<Map> selectByShowFlagTypeId(String showFlag, int typeId);

    @Select("SELECT\n" +
            "    t1.id,\n" +
            "    t1.title,\n" +
            "    t1.summary,\n" +
            "    t1.create_id,\n" +
            "    t1.create_date,\n" +
            "    t1.view_times,\n" +
            "    t1.like_times,\n" +
            "    t1.type_id,\n" +
            "    t1.index_picture,\n" +
            "    t2.nickname AS create_name,\n" +
            "    t2.prophoto AS prophoto,\n" +
            "    t3.name     AS type_name\n" +
            "FROM\n" +
            "    artical t1\n" +
            "LEFT JOIN\n" +
            "    user t2\n" +
            "ON\n" +
            "    t1.create_id = t2.ACCOUNT\n" +
            "LEFT JOIN\n" +
            "    TYPE t3\n" +
            "ON\n" +
            "    t1.type_id = t3.id\n" +
            "WHERE\n" +
            "    t1.id IN\n" +
            "    (\n" +
            "        SELECT\n" +
            "            artical_id\n" +
            "        FROM\n" +
            "            articaltag\n" +
            "        WHERE\n" +
            "            articaltag.tag_id=#{tagId})\n" +
            "AND t1.show_flag = #{showFlag}\n" +
            "ORDER BY\n" +
            "    t1.id DESC")
    List<Map> selectByShowFlagTagId(String showFlag, int tagId);

    @Select("select year(create_date) as year,count(1) as count  from artical  where show_flag=#{showFlag} group by year(create_date) order by year(create_date) desc")
    List<Map> selectYearAndCount(String showFlag);

    @Select("select * from artical where create_date LIKE CONCAT(#{year},'%') and show_flag=#{showFlag} order by create_date desc limit 0,#{count}")
    List<Artical> selectByYearAndShowFlag(String year, String showFlag, int count);

    @Select("SELECT\n" +
            "  t1.id,\n" +
            "  t1.title,\n" +
            "  t1.summary,\n" +
            "  t1.create_id,\n" +
            "  t1.create_date,\n" +
            "  t1.view_times,\n" +
            "  t1.like_times,\n" +
            "  t1.type_id,t1.index_picture,t1.view_times,t1.like_times,t1.reprint_flag,\n" +
            "  t2.nickname AS create_name,\n" +
            "  t2.prophoto AS prophoto,\n" +
            "  t3.name AS type_name\n" +
            "FROM artical t1\n" +
            "  LEFT JOIN user t2\n" +
            "    ON t1.create_id = t2.ACCOUNT\n" +
            "  LEFT JOIN TYPE t3\n" +
            "    ON t1.type_id = t3.id\n" +
            "WHERE t1.show_flag = #{showFlag} and t1.create_date like CONCAT(#{year},'%') order by t1.id desc")
    List<Map> selectByShowFlagAndYear(String showFlag, int year);

    @Select("SELECT * FROM ARTICAL WHERE ID<#{id} ORDER BY ID  DESC LIMIT 1")
    Artical findLastArtical(int id);

    @Select("SELECT * FROM ARTICAL WHERE ID>#{id} ORDER BY ID  ASC LIMIT 1")
    Artical findNextArtical(int id);

    @Update("update artical set like_times=#{liketimes},view_times=#{viewtimes} where id=#{id}")
    void updateLVTimesById(int id, int liketimes, int viewtimes);
}
